WITH PROC as (
    select PROCSCHEMA, PROCNAME, rd.bname as PKGNAME
    from SYSCAT.PROCEDURES p    
    join syscat.routinedep rd    
    on( p.PROCSCHEMA = rd.routineschema and p.specificname = rd.routinename)
),

RPL (
    ROOTTYPE, ROOTSCHEMA, ROOTNAME, LEVEL, SCHEMA, NAME, DTYPE,
    DEFINER, BTYPE, BSCHEMA, BNAME, TABAUTH
) AS
      (
         SELECT
            BTYPE AS ROOTTYPE, BSCHEMA AS ROOTSCHEMA, BNAME AS ROOTNAME,
            1 as LEVEL, VIEWSCHEMA as SCHEMA, VIEWNAME as NAME, DTYPE, DEFINER, BTYPE, BSCHEMA,
            BNAME, TABAUTH
          FROM SYSCAT.VIEWDEP ROOT
      UNION ALL
         SELECT
            ROOTTYPE, ROOTSCHEMA, ROOTNAME, PARENT.LEVEL+1, CHILD.VIEWSCHEMA as SCHEMA,
            CHILD.VIEWNAME as NAME, CHILD.DTYPE, CHILD.DEFINER, CHILD.BTYPE,
            CHILD.BSCHEMA, CHILD.BNAME, CHILD.TABAUTH
          FROM RPL PARENT, SYSCAT.VIEWDEP CHILD
          WHERE PARENT.SCHEMA = CHILD.BSCHEMA
          AND PARENT.NAME = CHILD.BNAME
            AND PARENT.LEVEL < 5
    UNION ALL
         SELECT
            BTYPE AS ROOTTYPE, BSCHEMA AS ROOTSCHEMA, BNAME AS ROOTNAME,
            1 as LEVEL, FUNCSCHEMA as SCHEMA, FUNCNAME as NAME, 'F' as DTYPE, '?' as DEFINER, BTYPE, BSCHEMA,
            BNAME, TABAUTH
          FROM SYSCAT.FUNCDEP ROOT
      UNION ALL
         SELECT
            ROOTTYPE, ROOTSCHEMA, ROOTNAME, PARENT.LEVEL+1, CHILD.FUNCSCHEMA as SCHEMA,
            CHILD.FUNCNAME as NAME, 'F' as DTYPE, '?' as DEFINER, CHILD.BTYPE,
            CHILD.BSCHEMA, CHILD.BNAME, CHILD.TABAUTH
          FROM RPL PARENT, SYSCAT.FUNCDEP CHILD
          WHERE PARENT.SCHEMA = CHILD.BSCHEMA
          AND PARENT.NAME = CHILD.BNAME
            AND PARENT.LEVEL < 5
    UNION ALL
         SELECT
            BTYPE AS ROOTTYPE, BSCHEMA AS ROOTSCHEMA, BNAME AS ROOTNAME,
            1 as LEVEL, PROC.PROCSCHEMA as SCHEMA, PROC.PROCNAME as NAME, 'P' as DTYPE, '?' as DEFINER, BTYPE, BSCHEMA,
            BNAME, TABAUTH
          FROM SYSCAT.PACKAGEDEP ROOT, PROC PROC
          where PROC.PROCSCHEMA = ROOT.PKGSCHEMA
          and PROC.PKGNAME = ROOT.PKGNAME 
      UNION ALL
         SELECT
            ROOTTYPE, ROOTSCHEMA, ROOTNAME, PARENT.LEVEL+1, PROC.PROCSCHEMA as SCHEMA,
            PROC.PROCNAME as NAME, 'P' as DTYPE, '?' as DEFINER, CHILD.BTYPE,
            CHILD.BSCHEMA, CHILD.BNAME, CHILD.TABAUTH
          FROM RPL PARENT, SYSCAT.PACKAGEDEP CHILD, PROC PROC
          WHERE PARENT.SCHEMA = CHILD.BSCHEMA
          AND PARENT.NAME = CHILD.BNAME
          and PROC.PROCSCHEMA = CHILD.PKGSCHEMA
          AND PROC.PKGNAME = CHILD.PKGNAME 
            AND PARENT.LEVEL < 5
      )
SELECT  distinct ROOTNAME, LEVEL, ROOTTYPE, ROOTSCHEMA,  RPL.SCHEMA, 
    CASE WHEN DTYPE = 'F'     
    THEN (SELECT ROUTINENAME FROM SYSCAT.ROUTINES WHERE ROUTINESCHEMA = RPL.SCHEMA AND SPECIFICNAME = RPL.NAME )     
    ELSE RPL.NAME 
    END AS NAME,
    DTYPE, DEFINER, BTYPE, BSCHEMA, 
    CASE WHEN BTYPE = 'F'     
    THEN (SELECT ROUTINENAME FROM SYSCAT.ROUTINES WHERE ROUTINESCHEMA = BSCHEMA AND SPECIFICNAME = BNAME )     
    ELSE BNAME 
    END AS BNAME, 
    TABAUTH
FROM RPL

--where ROOTSCHEMA = OBJ.SCHEMA 
--    and ROOTNAME = OBJ.NAME
--ORDER BY ROOTTYPE, ROOTSCHEMA, ROOTNAME, LEVEL
--@
